BPT Example Usage¶

0. Summary¶

A. Description¶

  1. Case 1: General data I/O with a custom bpt.py script
  2. Case 2: Data joins with star rating
  3. Case 3: Case Study: Mapping Projection Assumptions onto Choropleth Map

B. Where Can I Access Publicly-Available BPT datasets?¶

Historical BPT data from 2006-2018 can be downloaded in the CMS website under Medicare/Medicare Advantage Rates & Statistics/Data. For each year 2014 and after, the bid data is standardized and includes the following zip files:

  • CY20XX MA Pricing Data Release Technical Notes (PDF)
  • Dictionary
  • MA data
  • ESRD data
  • MSA data

Data in earlier years store the data corresponding to each workbook as individual zip files. The functions in bpt.py should be able to preprocess those.

C. The MA datasets¶

Within the MA zip file, there are serveral tab delimited text files with the prefix ma_X, where X is a number corresponding to a worksheet in the BPT workbook. Each number corresponds to the following fields:

  • ma_1: Base Period Experience and Projection Assumptions
  • ma_2: MA Projected Allowed Costs PMPM
  • ma_3: MA Projected Cost Sharing PMPM
  • ma_4: MA Projected Revenue Requirement PMPM
  • ma_5_cnty_obsXXXXXX_XXXXXX_20XX: MA Benchmark PMPM by county, observations, and year
  • ma_6: MA Bid Summary
  • ma_7: Optional Supplemental Benefits

1. BPT Data Loading and Preprocessing¶

A. Motivation¶

There are several files that are required to analyze the bid data in its full context. The bpt.py module enables the user to access the bid data more easily, where all *.zip files in a single directory. The goal of the module was to save time on I/O to conduct faster data analyses.

B. Data directory structure¶

To use this library out of the box, it is suggested that BPT data directory structure should be as followed:

data/
  |- 20XX/
       |- BPT20XX_ma_0.zip

C. BPT usage¶

The BPT class takes in a single paramter dir_to_query:str, which is a directory that can contain multiple objects.

Below are the main operations and the associated functions in the python script:

  1. load_data_dict(dirpath: str) -> pd.DataFrame: Function that reads in the data dictionary containing human-readable field names and return a dataframe that maps the headers for each text file (ma_X) to the human-readable field.
  2. load_sheet(dirpath: str, objname:str) -> pd.DataFrame: Function that loads the zipped text files into pandas Dataframes.
  3. load_county_data(dirpath: str, objname:str="ma_5") -> pd.DataFrame: Function that reads in the benchmark data, which consists of the several text files with the ma_5_... prefix, as a single pandas dataframe.
  4. replace_field_to_name(meta: pd.DataFrame, df: pd.DataFrame) -> pd.DataFrame: Function that replaces the column headers (excel column locations) to human-readable column names.
  5. BPT: A class corresponding to a given year for the BPT data. Contains the year, meta data, and each workbook with the structure ma_X. Note that entries without a Bid ID are dropped.

D. Example: Loading 2015 BPT data¶

Let's load up the 2015 BPT data as an example.

In [ ]:
from bpt import BPT
import pandas as pd
import numpy as np
In [ ]:
dir_to_query = '/mnt/c/Users/ScottCampit_nzmndyg/Projects/bid-pricing-tool/data/2015/'
BPT2015 = BPT(dir_to_query)

LOADING 2015 BPT DATA
LOADING BASE PERIOD EXPERIENCE ('ma_1') DATA
LOADING PROJECTED ALLOWED COST ('ma_2') DATA
LOADING PROJECTED COST SHARING ('ma_3') DATA
LOADING PROJECTED REV REQUIREMENT ('ma_4') DATA
LOADING BENCHMARK ('ma_5') DATA
LOADING BID SUMMARY ('ma_6') DATA
LOADING OPTIONAL SUPPLEMENTAL BENEFITS ('ma_7') DATA

To view MA Bid Summary data (stored in ma_6), call the object in the BPT class. This will return a pandas dataframe that can be further refined (e.g. fillnas, ensure datatypes are correct, etc.)

In [ ]:
pd.set_option('display.max_columns', None)
bid_summary = BPT2015.ma_6
bid_summary
Out[ ]:
BID ID (H-number, Plan ID, Segment ID) Contract Year (2015) Version Number Medicare-covered Net medical cost( MA Bid Summary!D0025) Medicare-covered Non-medical expense( MA Bid Summary!D0027) Medicare-covered Gain / loss margin( MA Bid Summary!D0028) Total Medicare-covered revenue requirement( MA Bid Summary!D0029) Plan A/B Bid Summary: Standardized A/B Benchmark( MA Bid Summary!D0031) Plan A/B Bid Summary: Plan A/B Benchmark( MA Bid Summary!D0032) Plan A/B Bid Summary: Non-ESRD Risk Factor( MA Bid Summary!D0033) Plan A/B Bid Summary: Conversion Factor( MA Bid Summary!D0034) Date Prepared( MA Bid Summary!D0055) Maximum Pt B premium buydown amt., per CMS( MA Bid Summary!E0014) Supplemental Net medical cost( MA Bid Summary!E0025) Supplemental Non-medical expense( MA Bid Summary!E0027) Supplemental Gain / loss margin( MA Bid Summary!E0028) Total Supplemental revenue requirement( MA Bid Summary!E0029) MA Rebate - Medical PMPM Alloc( MA Bid Summary!I0023) PMPM Medical Allocation to Reduce A/B Cost Share( MA Bid Summary!I0025) PMPM Medical Allocation to Other Mand Supp Benefits( MA Bid Summary!I0026) PMPM Medical Allocation to Pt B Premium Buydown( MA Bid Summary!I0027) PMPM Medical Allocation to Pt D Basic Premium Buydown( MA Bid Summary!I0028) PMPM Medical Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!I0029) PMPM Medical Allocation to Total( MA Bid Summary!I0030) MA Rebate - PMPM Admin Allocation( MA Bid Summary!J0023) PMPM Admin Allocation to Reduce A/B Cost Share( MA Bid Summary!J0025) PMPM Admin Allocation to Other Mand Supp Benefits( MA Bid Summary!J0026) PMPM Admin Allocation to Pt B Premium Buydown( MA Bid Summary!J0027) PMPM Admin Allocation to Pt D Basic Premium Buydown( MA Bid Summary!J0028) PMPM Admin Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!J0029) PMPM Admin Allocation Total( MA Bid Summary!J0030) MA Rebate - PMPM Allocation Gain/Loss( MA Bid Summary!K0023) Reduce A/B Cost Share - PMPM Allocation Gain/Loss( MA Bid Summary!K0025) Other Mandatory Supplemental Benefits - PMPM Allocation Gain/Loss( MA Bid Summary!K0026) Pt B Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0027) Pt D Basic Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0028) Pt D Suppl Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0029) Total PMPM Allocation Gain/Loss( MA Bid Summary!K0030) PMPM Rebate Allocation for Part B Premium( MA Bid Summary!L0013) Part B Rebate Allocation - rounded to one decimal( MA Bid Summary!L0014) Total PMPM Allocation to MA Rebate( MA Bid Summary!L0023) Total PMPM Allocation to Reduce A/B Cost Share( MA Bid Summary!L0025) Total PMPM Allocation to Other Mand Supp Benefits( MA Bid Summary!L0026) Total PMPM Allocation to Pt B Premium Buydown( MA Bid Summary!L0027) Total PMPM Allocation to Pt D Basic Premium Buydown( MA Bid Summary!L0028) Total PMPM Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!L0029) Total PMPM Allocation( MA Bid Summary!L0030) MA Unallocated rebate( MA Bid Summary!L0031) Max Value for Reduce A/B Cost Share( MA Bid Summary!M0025) Max Value for Other Mand Supp Benefits( MA Bid Summary!M0026) Max Value for Pt B Premium Buydown( MA Bid Summary!M0027) Other Information - Rebate Allocations Reduce A/B Cost Sharing (max. value=$0.00)( MA Bid Summary!R0013) Other Information - Rebate Allocations Other A/B Mand Suppl Benefits (max. value=$0.00)( MA Bid Summary!R0014) Estimated Plan Premium A/B Mandatory Supplemental revenue requirements( MA Bid Summary!R0022) Estimated Plan Premium Less A/B Cost Sharing( MA Bid Summary!R0024) Estimated Plan Premium Less Other Mand Supplemental Benefits( MA Bid Summary!R0025) Estimated Plan Premium A/B Mandatory Supplemental Premium( MA Bid Summary!R0027) Estimated Plan Premium Basic MA Premium( MA Bid Summary!R0029) Estimated Plan Premium MA Enrollee Premium( MA Bid Summary!R0030) Estimated Plan Premium Rounded MA Premium Subtotal( MA Bid Summary!R0031) Estimated Plan Premium A/B rebates allocated to Pt D Basic Premium( MA Bid Summary!R0035) Estimated Plan Premium A/B rebates for Pt D Basic Premium( MA Bid Summary!R0036) Estimated Plan Premium A/B Rebates allocated to Pt D Suppl Premium( MA Bid Summary!R0041) Estimated Plan Premium A/b Rebates for Pt D Suppl Premium( MA Bid Summary!R0042)
0 H0028_001_0 2015 3 473.2208 103.6732 -15.27038499 561.6236 733.0343 668.4790 0.913336 0.911934 8/1/2014 104.9 26.8559 5.8836 -0.86661501 31.8729 NaN 18.9921 7.8529774 0.0 25.5 12.1 64.445 NaN 4.1608 1.720431 NaN NaN NaN 5.8812 NaN -0.6129 -0.253408117 NaN NaN NaN -0.8663 0.0 0.0 69.46 22.54 9.32 0.0 25.5 12.1 69.46 0 22.54 9.33 104.9 22.54 9.32 31.87 -22.54 -9.32 0.01 0.0 0.01 0.0 25.5 25.5 12.1 12.1
1 H0028_002_0 2015 3 537.4973 104.6150 -48.05239067 594.0599 763.8931 701.8609 0.921151 0.918795 8/1/2014 104.9 28.3848 5.5246 -2.537609328 31.3718 NaN 20.5748 7.808306902 0.0 32.2 6.5 67.0832 NaN 4.0046 1.519758 NaN NaN NaN 5.5243 NaN -1.8394 -0.698064508 NaN NaN NaN -2.5375 0.0 0.0 70.07 22.74 8.63 0.0 32.2 6.5 70.07 0 22.74 8.63 104.9 22.74 8.63 31.37 -22.74 -8.63 0.00 0.0 0.00 0.0 32.2 32.2 6.5 6.5
2 H0028_003_0 2015 3 * 101.4875 * 711.8527 767.3700 793.8604 1.038901 1.034521 8/1/2014 104.9 * 1.9828 * 13.9075 NaN * * 0.0 37.5 1.9 * NaN 0.8925 1.090646 NaN NaN NaN 1.9831 NaN * * NaN NaN NaN * 0.0 0.0 53.31 6.26 7.65 0.0 37.5 1.9 53.31 0 6.26 7.65 104.9 6.26 7.65 13.91 -6.26 -7.65 0.00 0.0 0.00 0.0 37.5 37.5 1.9 1.9
3 H0028_004_0 2015 3 448.1032 87.0015 -26.16840231 508.9363 748.7868 650.7809 0.878000 0.869114 8/1/2014 104.9 61.7365 11.9865 -3.605297693 70.1177 NaN 50.1252 11.59579136 0.0 7.0 15.1 83.821 NaN 9.7321 2.251382 NaN NaN NaN 11.9834 NaN -2.9272 -0.677172861 NaN NaN NaN -3.6044 0.0 0.0 92.20 56.93 13.17 0.0 7.0 15.1 92.20 0 56.93 13.19 104.9 56.93 13.17 70.12 -56.93 -13.17 0.02 0.0 0.02 0.0 7.0 7.0 15.1 15.1
4 H0028_801_0 2015 3 520.2695 63.3785 -22.98709543 560.6609 750.6383 741.2694 0.988836 0.987519 7/9/2014 104.9 108.9429 13.2713 -4.81342868 117.4007 NaN 18.6427 90.29955327 0.0 0.0 0.0 108.9422 NaN 2.2710 11.000157 NaN NaN NaN 13.2712 NaN -0.8237 -3.98971 NaN NaN NaN -4.8134 0.0 0.0 117.40 20.09 97.31 0.0 0.0 0.0 117.40 0 20.09 97.31 104.9 20.09 97.31 117.40 -20.09 -97.31 0.00 0.0 0.00 0.0 NaN 0.0 NaN 0.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
3541 R7444_001_0 2015 2 613.9036 89.7746 60.71346891 764.3917 789.2402 775.6206 0.986331 0.982743 8/4/2014 104.9 13.9719 2.0432 1.381784099 17.3969 NaN 5.8628 0 0.0 0.0 0.0 5.8628 NaN 0.8574 0.000000 NaN NaN NaN 0.8574 NaN 0.5798 0 NaN NaN NaN 0.5798 0.0 0.0 7.30 7.30 0.00 0.0 0.0 0.0 7.30 0 9.23 8.17 104.9 7.30 0.00 17.40 -7.30 0.00 10.10 0.0 10.10 10.1 0.0 0.0 0.0 0.0
3542 R9896_008_0 2015 4 1208.7383 125.8367 268.6700061 1603.2450 733.5164 1665.9317 2.273393 2.271158 8/4/2014 104.9 23.6332 2.4604 5.253025215 31.3466 NaN 7.7278 15.90797322 0.0 0.0 0.0 23.6358 NaN 0.8045 1.656112 NaN NaN NaN 2.4606 NaN 1.7177 3.535914431 NaN NaN NaN 5.2536 0.0 0.0 31.35 10.25 21.10 0.0 0.0 0.0 31.35 0 10.25 21.10 104.9 10.25 21.10 31.35 -10.25 -21.10 0.00 0.0 0.00 0.0 0.0 0.0 0.0 0.0
3543 R9896_009_0 2015 5 826.765 109.0657 93.08431431 1028.9150 733.5164 1138.0844 1.559652 1.551546 8/5/2014 104.9 31.1729 4.1123 3.509711688 38.7949 NaN 19.6945 11.47442172 0.0 15.8 0.0 46.969 NaN 2.5981 1.513689 NaN NaN NaN 4.1118 NaN 2.2174 1.291889038 NaN NaN NaN 3.5093 0.0 0.0 54.59 24.51 14.28 0.0 15.8 0.0 54.59 0 24.51 14.28 104.9 24.51 14.28 38.79 -24.51 -14.28 0.00 0.0 0.00 0.0 15.8 15.8 0.0 0.0
3544 R9896_012_0 2015 5 516.5867 87.6878 60.48050404 664.7550 733.5164 707.4190 0.969065 0.964421 8/5/2014 104.9 17.5129 2.9727 2.050365626 22.5360 NaN 12.426 4.15752976 0.0 0.0 0.0 16.5835 NaN 2.1092 0.705718 NaN NaN NaN 2.8150 NaN 1.4548 0.486751806 NaN NaN NaN 1.9415 0.0 0.0 21.34 15.99 5.35 0.0 0.0 0.0 21.34 0 15.99 6.55 104.9 15.99 5.35 22.54 -15.99 -5.35 1.20 0.0 1.20 1.2 0.0 0.0 0.0 0.0
3545 R9896_021_0 2015 4 818.5822 111.2960 26.37684447 956.2550 733.5164 975.0843 1.330701 1.329329 8/5/2014 104.9 8.0674 1.0969 0.259953787 9.4243 NaN 1.1728 6.891035019 0.0 0.0 0.0 8.0638 NaN 0.1595 0.936918 NaN NaN NaN 1.0964 NaN 0.0378 0.222047046 NaN NaN NaN 0.2598 0.0 0.0 9.42 1.37 8.05 0.0 0.0 0.0 9.42 0 1.37 8.05 104.9 1.37 8.05 9.42 -1.37 -8.05 0.00 0.0 0.00 0.0 0.0 0.0 0.0 0.0

3546 rows × 64 columns

Let's now get columns with cost sharing information.

In [ ]:
cost_share_2015 = bid_summary.filter(like='Shar')
cost_share_2015
Out[ ]:
PMPM Medical Allocation to Reduce A/B Cost Share( MA Bid Summary!I0025) PMPM Admin Allocation to Reduce A/B Cost Share( MA Bid Summary!J0025) Reduce A/B Cost Share - PMPM Allocation Gain/Loss( MA Bid Summary!K0025) Total PMPM Allocation to Reduce A/B Cost Share( MA Bid Summary!L0025) Max Value for Reduce A/B Cost Share( MA Bid Summary!M0025) Other Information - Rebate Allocations Reduce A/B Cost Sharing (max. value=$0.00)( MA Bid Summary!R0013) Estimated Plan Premium Less A/B Cost Sharing( MA Bid Summary!R0024)
0 18.9921 4.1608 -0.6129 22.54 22.54 22.54 -22.54
1 20.5748 4.0046 -1.8394 22.74 22.74 22.74 -22.74
2 * 0.8925 * 6.26 6.26 6.26 -6.26
3 50.1252 9.7321 -2.9272 56.93 56.93 56.93 -56.93
4 18.6427 2.2710 -0.8237 20.09 20.09 20.09 -20.09
... ... ... ... ... ... ... ...
3541 5.8628 0.8574 0.5798 7.30 9.23 7.30 -7.30
3542 7.7278 0.8045 1.7177 10.25 10.25 10.25 -10.25
3543 19.6945 2.5981 2.2174 24.51 24.51 24.51 -24.51
3544 12.426 2.1092 1.4548 15.99 15.99 15.99 -15.99
3545 1.1728 0.1595 0.0378 1.37 1.37 1.37 -1.37

3546 rows × 7 columns

E. Example: Data Cleaning¶

We can do some data quality checks and data cleaning.

For the following example, we will examine the BPT projection assumptions or projection factor. These projection factors are values that are used to project the base period data to the contract period.

  • There are projection assumptions for each of the 18 service category (e.g. Inpatient facility, Skilled Nursing Facility, etc).
  • Additionally, there are 3 categories of projection assumptions: Util/1000, Unit Cost, and Additive Adjustments
  • Together, the service category + projection assumption categories make up the columns while the rows consist of the bid ids.

Expected data range¶

The expected values should be a positive floating point value. None of the entries should be zero (but surprisingly, there are, and there's no documentation why that is!)

Missing values¶

For instance, there are asterisks in some of the row entries. Per the 2015 Bid Pricing Tool Technical Notes:

"Any information that could be used to identify Medicare beneficiaries or other individuals. As part of this exclusion, an [asterisk] indicates that a data field has been suppressed because there are fewer than 11 Medicare beneficiaries in the data field or for necessary complimentary cell suppression."

Additionally, there are entries where there is no entry at all, and thus the cell contains NaNs.

What we'll do:¶

  1. Get the projection assumptions
  2. Set zeros to ones and fill in missing values with 1
  3. Separate out the projection assumptions by category (Util/1000, Unit Cost, Additive)
In [ ]:
base_period = BPT2015.ma_1

First, get projection assumptions.

In [ ]:
prj_asmpt_pattern = "BID ID|MA Base!J00(2[7-9]|3[0-9]|4[0-4])|MA Base!K00(2[7-9]|3[0-9]|4[0-4])|MA Base!L00(2[7-9]|3[0-9]|4[0-4])|MA Base!M00(2[7-9]|3[0-9]|4[0-4])|MA Base!N00(2[7-9]|3[0-9]|4[0-4])|MA Base!O00(2[7-9]|3[0-9]|4[0-4])|MA Base!P00(2[7-9]|3[0-9]|4[0-4])|MA Base!Q00(2[7-9]|3[0-9]|4[0-4])"
prj_asmpt = base_period.loc[:, base_period.columns.str.contains(pat=prj_asmpt_pattern, regex=True)]
prj_asmpt = prj_asmpt.set_index('BID ID (H-number, Plan ID, Segment ID)')
prj_asmpt.head(3)

/tmp/ipykernel_3832/4245737771.py:2: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract.
  prj_asmpt = base_period.loc[:, base_period.columns.str.contains(pat=prj_asmpt_pattern, regex=True)]
Out[ ]:
Inpatient Facility [Util/1000 Trend]( MA Base!J0027) Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) Home Health [Util/1000 Trend]( MA Base!J0029) Ambulance [Util/1000 Trend]( MA Base!J0030) DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) OP Facility - Other [Util/1000 Trend]( MA Base!J0034) Professional [Util/1000 Trend]( MA Base!J0035) Part B Rx [Util/1000 Trend]( MA Base!J0036) Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) Health & Education [Util/1000 Trend]( MA Base!J0042) Other Non-Covered [Util/1000 Trend]( MA Base!J0043) COB/Subrg. [Util/1000 Trend]( MA Base!J0044) Inpatient Facility [Benefit Plan Change]( MA Base!K0027) Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) Home Health [Benefit Plan Change]( MA Base!K0029) Ambulance [Benefit Plan Change]( MA Base!K0030) DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) OP Facility - Other [Benefit Plan Change]( MA Base!K0034) Professional [Benefit Plan Change]( MA Base!K0035) Part B Rx [Benefit Plan Change]( MA Base!K0036) Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) Health & Education [Benefit Plan Change]( MA Base!K0042) Other Non-Covered [Benefit Plan Change]( MA Base!K0043) COB/Subrg. [Benefit Plan Change]( MA Base!K0044) Inpatient Facility [Population Change]( MA Base!L0027) Skilled Nursing Facility [Population Change]( MA Base!L0028) Home Health [Population Change]( MA Base!L0029) Ambulance [Population Change]( MA Base!L0030) DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) OP Facility - Emergency [Population Change]( MA Base!L0032) OP Facility - Surgery [Population Change]( MA Base!L0033) OP Facility - Other [Population Change]( MA Base!L0034) Professional [Population Change]( MA Base!L0035) Part B Rx [Population Change]( MA Base!L0036) Other Medicare Covered [Population Change]( MA Base!L0037) Transportation (Non-Covered) [Population Change]( MA Base!L0038) Dental (Non-Covered) [Population Change]( MA Base!L0039) Vision (Non-Covered) [Population Change]( MA Base!L0040) Hearing (Non-Covered) [Population Change]( MA Base!L0041) Health & Education [Population Change]( MA Base!L0042) Other Non-Covered [Population Change]( MA Base!L0043) COB/Subrg. [Population Change]( MA Base!L0044) Inpatient Facility [Other Factor]( MA Base!M0027) Skilled Nursing Facility [Other Factor]( MA Base!M0028) Home Health [Other Factor]( MA Base!M0029) Ambulance [Other Factor]( MA Base!M0030) DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) OP Facility - Emergency [Other Factor]( MA Base!M0032) OP Facility - Surgery [Other Factor]( MA Base!M0033) OP Facility - Other [Other Factor]( MA Base!M0034) Professional [Other Factor]( MA Base!M0035) Part B Rx [Other Factor]( MA Base!M0036) Other Medicare Covered [Other Factor]( MA Base!M0037) Transportation (Non-Covered) [Other Factor]( MA Base!M0038) Dental (Non-Covered) [Other Factor]( MA Base!M0039) Vision (Non-Covered) [Other Factor]( MA Base!M0040) Hearing (Non-Covered) [Other Factor]( MA Base!M0041) Health & Education [Other Factor]( MA Base!M0042) Other Non-Covered [Other Factor]( MA Base!M0043) COB/Subrg. [Other Factor]( MA Base!M0044) Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) Home Health [Additive Adjustment Util/1000]( MA Base!P0029) Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) Professional [Additive Adjustment Util/1000]( MA Base!P0035) Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) Home Health [Additive Adjustment PMPM]( MA Base!Q0029) Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) Professional [Additive Adjustment PMPM]( MA Base!Q0035) Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044)
BID ID (H-number, Plan ID, Segment ID)
H0028_001_0 1.041586 1.040309 1.085316 1.085316 1.085316 1.064892 1.058333 1.062038 1.034823 1.038227 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.965442 1.000000 1.0 0.992343 1.000000 1.0 0.987336 1.001475 1.002775 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.064938 1.111958 1.047501 1.056095 1.051343 1.025000 1.023171 1.021300 1.024814 1.023619 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.946205 1.029156 1.021117 1.021117 1.021117 0.968596 0.973498 0.970729 0.992119 0.991759 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.021212 1.029386 1.000603 1.000322 0.999001 1.027579 1.025603 1.026627 1.018506 1.020768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.032320 1.073888 1.095386 1.093760 1.096505 1.014410 1.004024 1.011607 0.970527 0.910285 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 -12643.60265 1908.905341 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 -4.830103 0.46735 NaN
H0028_002_0 1.048467 1.033212 1.110564 1.110564 1.110564 1.078348 1.068757 1.072278 1.039763 1.051768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.965887 1.000000 1.0 0.992552 1.000000 1.0 0.988270 1.000268 1.003637 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.086141 1.198642 1.073244 1.146072 1.070351 1.074450 1.025969 1.033564 1.032510 1.024794 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.946038 1.035478 1.010779 1.010779 1.010779 0.993476 0.991236 0.992058 0.984504 0.990402 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.011443 1.029042 1.007127 1.005176 1.004994 1.023802 1.022753 1.020853 1.017948 1.018910 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.034544 1.067340 1.106669 1.098973 1.101808 1.011492 0.996657 1.008320 0.981439 0.923308 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 -2860.545342 2832.475088 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 -1.221185 0.56387 NaN
H0028_003_0 1.035891 1.045731 1.096176 1.096176 1.096176 1.068046 1.064381 1.065199 1.049049 1.062640 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.983970 1.038864 1.0 0.985140 1.010346 1.0 0.999905 1.003564 0.996684 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.126589 1.154194 1.011559 1.125635 1.068715 1.053648 1.022186 1.021385 1.015208 1.018191 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.949851 1.034484 1.014373 1.014373 1.014373 1.006236 1.002903 1.003647 0.988906 1.003002 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.014657 1.029904 1.005337 1.004158 1.004711 1.028985 1.027605 1.025852 1.019485 1.024157 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 1.009003 1.077537 1.087700 1.080784 1.098513 0.991736 1.019309 0.992896 0.999983 0.955871 1.0 1.0 1.0 1.0 1.0 1.0 1.0 NaN 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 * -81.000569 0 2213.447304 -19070.36114 0.0 0.0 0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0 0 * -0.261382 0 1.697966 -1.626392 NaN

Replace missing values.

In [ ]:
prj_asmpt = prj_asmpt.replace('*', 1)
prj_asmpt = prj_asmpt.replace(np.nan, 1)
prj_asmpt = prj_asmpt.replace(0, 1)
prj_asmpt = prj_asmpt.apply(lambda x: pd.to_numeric(x), axis=1, errors='coerce')
prj_asmpt.head(3)
Out[ ]:
Inpatient Facility [Util/1000 Trend]( MA Base!J0027) Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) Home Health [Util/1000 Trend]( MA Base!J0029) Ambulance [Util/1000 Trend]( MA Base!J0030) DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) OP Facility - Other [Util/1000 Trend]( MA Base!J0034) Professional [Util/1000 Trend]( MA Base!J0035) Part B Rx [Util/1000 Trend]( MA Base!J0036) Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) Health & Education [Util/1000 Trend]( MA Base!J0042) Other Non-Covered [Util/1000 Trend]( MA Base!J0043) COB/Subrg. [Util/1000 Trend]( MA Base!J0044) Inpatient Facility [Benefit Plan Change]( MA Base!K0027) Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) Home Health [Benefit Plan Change]( MA Base!K0029) Ambulance [Benefit Plan Change]( MA Base!K0030) DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) OP Facility - Other [Benefit Plan Change]( MA Base!K0034) Professional [Benefit Plan Change]( MA Base!K0035) Part B Rx [Benefit Plan Change]( MA Base!K0036) Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) Health & Education [Benefit Plan Change]( MA Base!K0042) Other Non-Covered [Benefit Plan Change]( MA Base!K0043) COB/Subrg. [Benefit Plan Change]( MA Base!K0044) Inpatient Facility [Population Change]( MA Base!L0027) Skilled Nursing Facility [Population Change]( MA Base!L0028) Home Health [Population Change]( MA Base!L0029) Ambulance [Population Change]( MA Base!L0030) DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) OP Facility - Emergency [Population Change]( MA Base!L0032) OP Facility - Surgery [Population Change]( MA Base!L0033) OP Facility - Other [Population Change]( MA Base!L0034) Professional [Population Change]( MA Base!L0035) Part B Rx [Population Change]( MA Base!L0036) Other Medicare Covered [Population Change]( MA Base!L0037) Transportation (Non-Covered) [Population Change]( MA Base!L0038) Dental (Non-Covered) [Population Change]( MA Base!L0039) Vision (Non-Covered) [Population Change]( MA Base!L0040) Hearing (Non-Covered) [Population Change]( MA Base!L0041) Health & Education [Population Change]( MA Base!L0042) Other Non-Covered [Population Change]( MA Base!L0043) COB/Subrg. [Population Change]( MA Base!L0044) Inpatient Facility [Other Factor]( MA Base!M0027) Skilled Nursing Facility [Other Factor]( MA Base!M0028) Home Health [Other Factor]( MA Base!M0029) Ambulance [Other Factor]( MA Base!M0030) DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) OP Facility - Emergency [Other Factor]( MA Base!M0032) OP Facility - Surgery [Other Factor]( MA Base!M0033) OP Facility - Other [Other Factor]( MA Base!M0034) Professional [Other Factor]( MA Base!M0035) Part B Rx [Other Factor]( MA Base!M0036) Other Medicare Covered [Other Factor]( MA Base!M0037) Transportation (Non-Covered) [Other Factor]( MA Base!M0038) Dental (Non-Covered) [Other Factor]( MA Base!M0039) Vision (Non-Covered) [Other Factor]( MA Base!M0040) Hearing (Non-Covered) [Other Factor]( MA Base!M0041) Health & Education [Other Factor]( MA Base!M0042) Other Non-Covered [Other Factor]( MA Base!M0043) COB/Subrg. [Other Factor]( MA Base!M0044) Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) Home Health [Additive Adjustment Util/1000]( MA Base!P0029) Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) Professional [Additive Adjustment Util/1000]( MA Base!P0035) Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) Home Health [Additive Adjustment PMPM]( MA Base!Q0029) Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) Professional [Additive Adjustment PMPM]( MA Base!Q0035) Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044)
BID ID (H-number, Plan ID, Segment ID)
H0028_001_0 1.041586 1.040309 1.085316 1.085316 1.085316 1.064892 1.058333 1.062038 1.034823 1.038227 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.965442 1.000000 1.0 0.992343 1.000000 1.0 0.987336 1.001475 1.002775 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.064938 1.111958 1.047501 1.056095 1.051343 1.025000 1.023171 1.021300 1.024814 1.023619 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.946205 1.029156 1.021117 1.021117 1.021117 0.968596 0.973498 0.970729 0.992119 0.991759 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.021212 1.029386 1.000603 1.000322 0.999001 1.027579 1.025603 1.026627 1.018506 1.020768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.032320 1.073888 1.095386 1.093760 1.096505 1.014410 1.004024 1.011607 0.970527 0.910285 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -12643.602650 1908.905341 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -4.830103 0.467350 1.0
H0028_002_0 1.048467 1.033212 1.110564 1.110564 1.110564 1.078348 1.068757 1.072278 1.039763 1.051768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.965887 1.000000 1.0 0.992552 1.000000 1.0 0.988270 1.000268 1.003637 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.086141 1.198642 1.073244 1.146072 1.070351 1.074450 1.025969 1.033564 1.032510 1.024794 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.946038 1.035478 1.010779 1.010779 1.010779 0.993476 0.991236 0.992058 0.984504 0.990402 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.011443 1.029042 1.007127 1.005176 1.004994 1.023802 1.022753 1.020853 1.017948 1.018910 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.034544 1.067340 1.106669 1.098973 1.101808 1.011492 0.996657 1.008320 0.981439 0.923308 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -2860.545342 2832.475088 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -1.221185 0.563870 1.0
H0028_003_0 1.035891 1.045731 1.096176 1.096176 1.096176 1.068046 1.064381 1.065199 1.049049 1.062640 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.983970 1.038864 1.0 0.985140 1.010346 1.0 0.999905 1.003564 0.996684 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.126589 1.154194 1.011559 1.125635 1.068715 1.053648 1.022186 1.021385 1.015208 1.018191 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.949851 1.034484 1.014373 1.014373 1.014373 1.006236 1.002903 1.003647 0.988906 1.003002 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.014657 1.029904 1.005337 1.004158 1.004711 1.028985 1.027605 1.025852 1.019485 1.024157 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.009003 1.077537 1.087700 1.080784 1.098513 0.991736 1.019309 0.992896 0.999983 0.955871 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -81.000569 1.0 2213.447304 -19070.361140 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -0.261382 1.0 1.697966 -1.626392 1.0

Separate projection assumptions by category.

In [ ]:
util_adj = prj_asmpt.filter(regex=('Util/1000 Trend|Benefit Plan Change|Population Change|/[Other Factor/]'), axis=1)
unitcost_adj = prj_asmpt.filter(regex='Unit Cost')
additive_adj = prj_asmpt.filter(regex='Additive Adjustment')
In [ ]:
additive_adj.head(3)
Out[ ]:
Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) Home Health [Additive Adjustment Util/1000]( MA Base!P0029) Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) Professional [Additive Adjustment Util/1000]( MA Base!P0035) Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) Home Health [Additive Adjustment PMPM]( MA Base!Q0029) Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) Professional [Additive Adjustment PMPM]( MA Base!Q0035) Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044)
BID ID (H-number, Plan ID, Segment ID)
H0028_001_0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -12643.602650 1908.905341 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -4.830103 0.467350 1.0
H0028_002_0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -2860.545342 2832.475088 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -1.221185 0.563870 1.0
H0028_003_0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -81.000569 1.0 2213.447304 -19070.361140 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -0.261382 1.0 1.697966 -1.626392 1.0

2. Data Joins with Star Rating Data¶

If we wanted to, we can merge the bid data with other data sources such as Star Rating using the BID ID number. We'll demonstrate how to do that with the 2015 BPT data loaded above.

First, let's load the 2015 Star Rating data, which can be found here

  • Folder name: 2015_Star_Ratings_and_Display_Measures.zip
  • File of interest: 2015 Star Ratings Fall Release (10_2014)/2015_Report_Card_Master_Table_2014_10_03_summary.csv

The functions below scrape Star Rating data from the CMS website.

In [ ]:
import os
import requests
import zipfile
import io
from bs4 import BeautifulSoup as bs

BASEURL = 'https://www.cms.gov/'

def check_if_file_exists(url:str=None, data_directory:str=None, save_file:bool=True):
    """
    Automatically checks whether or not the queried data is stored locally in the machine. 
        * If there is a record, then the file will not be saved.
        * Otherwise, the file will be saved at the directory specified.

    Note: if a directory is not supplied, the file will NOT be saved.
    """
    if None not in (data_directory, url, save_file):
        datastore = os.listdir(data_directory)
        if url.split('/')[-1] not in datastore:
            r = requests.get(url)
            filename = url.split('/')[-1]
            with open(data_directory+filename, 'wb') as file_:
                file_.write(r.content)
        else:
            return

def get_star_rating(
        STARPATH:str="Medicare/Prescription-Drug-Coverage/PrescriptionDrugCovGenIn/PerformanceData", \
        SAVE_PATH:str=None,#Example: "C:/Users/ScottCampit/Projects/quadrant-chart/data/", \
        year:str=None
    ):
    """Scrapes Star Rating data from the CMS website."""
    r = requests.get(BASEURL+STARPATH)
    soup = bs(r.text, 'html.parser')
    all_data_urls = list()
    for name in soup.findAll('a', href=True):
        zipurl = name['href']
        if zipurl.endswith('.zip'):
            if any(filename in zipurl for filename in ('data', 'Data')):
                if year:
                    if any(filename in zipurl for filename in (year)):
                        all_data_urls.append(zipurl)
                else:
                    all_data_urls.append(zipurl)
            
    # Get the latest zip file containing the star rating data.
    latest_arxiv = all_data_urls[0]
    new_r2 = requests.get(BASEURL+latest_arxiv)
    check_if_file_exists(url=BASEURL+latest_arxiv, data_directory=SAVE_PATH)

    arxiv = zipfile.ZipFile(io.BytesIO(new_r2.content), 'r')
    for name in arxiv.namelist():
        if 'Summary' in name: # The relevant .csv file we need
            df = pd.read_csv(arxiv.open(name), skiprows=1)
            df = df.dropna(axis=1, how='all')
    
    return df
In [ ]:
star2015 = get_star_rating(SAVE_PATH=None, year='2015')
star2015.head(3)
Out[ ]:
Contract Number Organization Type Contract Name Organization Marketing Name Parent Organization SNP 2020 Disaster % 2021 Disaster % 2023 Part C Summary 2023 Part D Summary 2023 Overall
0 E3014 Employer/Union Only Direct Contract PDP PSERS HOP PROGRAM Pennsylvania Public School Employees Retiremen... Commonwealth of PA Pub Schools Retirement System No 100.0 1 Not Applicable 4.5 Not Applicable
1 E4744 Employer/Union Only Direct Contract PDP MODOT/MSHP MEDICAL AND LIFE INSURANCE PLAN MISSOURI DEPARTMENT OF TRANSPORTATION Missouri Highways and Transportation Commission No 100.0 1 Not Applicable 3.5 Not Applicable
2 H0022 Demo BUCKEYE COMMUNITY HEALTH PLAN, INC. Buckeye Health Plan - MyCare Ohio Centene Corporation No 100.0 0 Not Applicable Not enough data available Not enough data available

We can map Star Rating data by Contract Number to the bid ID. Unfortunately, we don't have higher resolution with the segment IDs.

In [ ]:
star2015['Contract Number'] = star2015['Contract Number'].str.rstrip()
In [ ]:
prj_asmpt['Contract ID'] = prj_asmpt.index.str.split(pat='_', n=1).str[0]
joined_prj_asmpt = pd.merge(star2015, prj_asmpt, how='inner', left_on='Contract Number', right_on='Contract ID')
joined_prj_asmpt = joined_prj_asmpt.drop(['Contract ID'], axis=1)
joined_prj_asmpt.head(3)
Out[ ]:
Contract Number Organization Type Contract Name Organization Marketing Name Parent Organization SNP 2020 Disaster % 2021 Disaster % 2023 Part C Summary 2023 Part D Summary 2023 Overall Inpatient Facility [Util/1000 Trend]( MA Base!J0027) Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) Home Health [Util/1000 Trend]( MA Base!J0029) Ambulance [Util/1000 Trend]( MA Base!J0030) DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) OP Facility - Other [Util/1000 Trend]( MA Base!J0034) Professional [Util/1000 Trend]( MA Base!J0035) Part B Rx [Util/1000 Trend]( MA Base!J0036) Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) Health & Education [Util/1000 Trend]( MA Base!J0042) Other Non-Covered [Util/1000 Trend]( MA Base!J0043) COB/Subrg. [Util/1000 Trend]( MA Base!J0044) Inpatient Facility [Benefit Plan Change]( MA Base!K0027) Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) Home Health [Benefit Plan Change]( MA Base!K0029) Ambulance [Benefit Plan Change]( MA Base!K0030) DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) OP Facility - Other [Benefit Plan Change]( MA Base!K0034) Professional [Benefit Plan Change]( MA Base!K0035) Part B Rx [Benefit Plan Change]( MA Base!K0036) Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) Health & Education [Benefit Plan Change]( MA Base!K0042) Other Non-Covered [Benefit Plan Change]( MA Base!K0043) COB/Subrg. [Benefit Plan Change]( MA Base!K0044) Inpatient Facility [Population Change]( MA Base!L0027) Skilled Nursing Facility [Population Change]( MA Base!L0028) Home Health [Population Change]( MA Base!L0029) Ambulance [Population Change]( MA Base!L0030) DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) OP Facility - Emergency [Population Change]( MA Base!L0032) OP Facility - Surgery [Population Change]( MA Base!L0033) OP Facility - Other [Population Change]( MA Base!L0034) Professional [Population Change]( MA Base!L0035) Part B Rx [Population Change]( MA Base!L0036) Other Medicare Covered [Population Change]( MA Base!L0037) Transportation (Non-Covered) [Population Change]( MA Base!L0038) Dental (Non-Covered) [Population Change]( MA Base!L0039) Vision (Non-Covered) [Population Change]( MA Base!L0040) Hearing (Non-Covered) [Population Change]( MA Base!L0041) Health & Education [Population Change]( MA Base!L0042) Other Non-Covered [Population Change]( MA Base!L0043) COB/Subrg. [Population Change]( MA Base!L0044) Inpatient Facility [Other Factor]( MA Base!M0027) Skilled Nursing Facility [Other Factor]( MA Base!M0028) Home Health [Other Factor]( MA Base!M0029) Ambulance [Other Factor]( MA Base!M0030) DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) OP Facility - Emergency [Other Factor]( MA Base!M0032) OP Facility - Surgery [Other Factor]( MA Base!M0033) OP Facility - Other [Other Factor]( MA Base!M0034) Professional [Other Factor]( MA Base!M0035) Part B Rx [Other Factor]( MA Base!M0036) Other Medicare Covered [Other Factor]( MA Base!M0037) Transportation (Non-Covered) [Other Factor]( MA Base!M0038) Dental (Non-Covered) [Other Factor]( MA Base!M0039) Vision (Non-Covered) [Other Factor]( MA Base!M0040) Hearing (Non-Covered) [Other Factor]( MA Base!M0041) Health & Education [Other Factor]( MA Base!M0042) Other Non-Covered [Other Factor]( MA Base!M0043) COB/Subrg. [Other Factor]( MA Base!M0044) Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) Home Health [Additive Adjustment Util/1000]( MA Base!P0029) Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) Professional [Additive Adjustment Util/1000]( MA Base!P0035) Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) Home Health [Additive Adjustment PMPM]( MA Base!Q0029) Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) Professional [Additive Adjustment PMPM]( MA Base!Q0035) Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044)
0 H0028 Local CCP CHA HMO, INC. Humana Humana Inc. Yes 100.0 41 4 4 4 1.041586 1.040309 1.085316 1.085316 1.085316 1.064892 1.058333 1.062038 1.034823 1.038227 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.965442 1.000000 1.0 0.992343 1.000000 1.0 0.987336 1.001475 1.002775 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.064938 1.111958 1.047501 1.056095 1.051343 1.025000 1.023171 1.021300 1.024814 1.023619 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.946205 1.029156 1.021117 1.021117 1.021117 0.968596 0.973498 0.970729 0.992119 0.991759 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.021212 1.029386 1.000603 1.000322 0.999001 1.027579 1.025603 1.026627 1.018506 1.020768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.032320 1.073888 1.095386 1.093760 1.096505 1.014410 1.004024 1.011607 0.970527 0.910285 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -12643.602650 1908.905341 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -4.830103 0.467350 1.0
1 H0028 Local CCP CHA HMO, INC. Humana Humana Inc. Yes 100.0 41 4 4 4 1.048467 1.033212 1.110564 1.110564 1.110564 1.078348 1.068757 1.072278 1.039763 1.051768 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.965887 1.000000 1.0 0.992552 1.000000 1.0 0.988270 1.000268 1.003637 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.086141 1.198642 1.073244 1.146072 1.070351 1.074450 1.025969 1.033564 1.032510 1.024794 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.946038 1.035478 1.010779 1.010779 1.010779 0.993476 0.991236 0.992058 0.984504 0.990402 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.011443 1.029042 1.007127 1.005176 1.004994 1.023802 1.022753 1.020853 1.017948 1.018910 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.034544 1.067340 1.106669 1.098973 1.101808 1.011492 0.996657 1.008320 0.981439 0.923308 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -2860.545342 2832.475088 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.000000 1.0 -1.221185 0.563870 1.0
2 H0028 Local CCP CHA HMO, INC. Humana Humana Inc. Yes 100.0 41 4 4 4 1.035891 1.045731 1.096176 1.096176 1.096176 1.068046 1.064381 1.065199 1.049049 1.062640 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.983970 1.038864 1.0 0.985140 1.010346 1.0 0.999905 1.003564 0.996684 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.126589 1.154194 1.011559 1.125635 1.068715 1.053648 1.022186 1.021385 1.015208 1.018191 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.949851 1.034484 1.014373 1.014373 1.014373 1.006236 1.002903 1.003647 0.988906 1.003002 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.014657 1.029904 1.005337 1.004158 1.004711 1.028985 1.027605 1.025852 1.019485 1.024157 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.009003 1.077537 1.087700 1.080784 1.098513 0.991736 1.019309 0.992896 0.999983 0.955871 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -81.000569 1.0 2213.447304 -19070.361140 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 -0.261382 1.0 1.697966 -1.626392 1.0

3. Case Study: Mapping Projection Assumptions onto Choropleth Map¶

This case-study examines the projection assumptions at the county-level.

Note that there are several steps to map the data to FIPs. We took this approach:

  1. Load the benchmark data (ma_5), which contains a mapping of the Bid IDs -> county.
  2. Load a county-FIPs mapping obtained here.
  3. Merge the two sources.
  4. Extract the projection assumptions from ma_1 using the mapped Bid ID <-> FIPs relationship.
  5. Look at specific projection assumptions that had high variance across plans.

The resulting dataframe is the chloropleth_outliers.csv, which contains the top 10 service categories for each projection assumption that had the highest variance across all bid IDs.

First, load the FIPs-mapped data.

In [ ]:
from urllib.request import urlopen
import requests
import json
from sklearn.preprocessing import MinMaxScaler

# Specialized libraries for geographic analysis
import geopandas as gpd
import branca.colormap as cm
import folium
import branca
In [ ]:
county_path = "/mnt/c/users/ScottCampit_nzmndyg/Projects/bid-pricing-tool/analysis/2015/chloropleth_outliers.csv"
county_df = pd.read_csv(county_path)
county_df.head(3)

/tmp/ipykernel_3832/1366550535.py:2: DtypeWarning: Columns (239,240,241,242,264,291,292,293,294,345) have mixed types. Specify dtype option on import or set low_memory=False.
  county_df = pd.read_csv(county_path)
Out[ ]:
Inpatient Facility Utilizers( MA Base!C0027) Skilled Nursing Facility Utilizers( MA Base!C0028) Home Health Utilizers( MA Base!C0029) Ambulance Utilizers( MA Base!C0030) DME/Prosthetics/Supplies Utilizers( MA Base!C0031) OP Facility - Emergency Utilizers( MA Base!C0032) OP Facility - Surgery Utilizers( MA Base!C0033) OP Facility - Other Utilizers( MA Base!C0034) Professional Utilizers( MA Base!C0035) Part B Rx Utilizers( MA Base!C0036) Other Medicare Covered Utilizers( MA Base!C0037) Transportation (Non-Covered) Utilizers( MA Base!C0038) Dental (Non-Covered) Utilizers( MA Base!C0039) Vision (Non-Covered) Utilizers( MA Base!C0040) Hearing (Non-Covered) Utilizers( MA Base!C0041) Health & Education Utilizers( MA Base!C0042) Other Non-Covered Utilizers( MA Base!C0043) Contract Number( MA Base!D0005) Plan ID( MA Base!D0006) Segment ID( MA Base!D0007) Contract Year (MA Base!D0008) Inpatient Facility Net PMPM( MA Base!D0027) Skilled Nursing Facility Net PMPM( MA Base!D0028) Home Health Net PMPM( MA Base!D0029) Ambulance Net PMPM( MA Base!D0030) DME/Prosthetics/Supplies Net PMPM( MA Base!D0031) OP Facility - Emergency Net PMPM( MA Base!D0032) OP Facility - Surgery Net PMPM( MA Base!D0033) OP Facility - Other Net PMPM( MA Base!D0034) Professional Net PMPM( MA Base!D0035) Part B Rx Net PMPM( MA Base!D0036) Other Medicare Covered Net PMPM( MA Base!D0037) Transportation (Non-Covered) Net PMPM( MA Base!D0038) Dental (Non-Covered) Net PMPM( MA Base!D0039) Vision (Non-Covered) Net PMPM( MA Base!D0040) Hearing (Non-Covered) Net PMPM( MA Base!D0041) Health & Education Net PMPM( MA Base!D0042) Other Non-Covered Net PMPM( MA Base!D0043) COB/Subrg. (Outside Claim system) Net PMPM( MA Base!D0044) Net PMPM Total Medical Expenses( MA Base!D0045) Base Period Summary CMS Revenue ESRD( MA Base!D0055) Base Period Summary Premium Revenue ESRD( MA Base!D0056) Base Period Summary Total Revenue ESRD( MA Base!D0057) Base Period Summary Net Medical Expenses ESRD( MA Base!D0059) Base Period Summary Member Months ESRD( MA Base!D0061) Base Period Summary PMPMs: Revenue PMPM ESRD( MA Base!D0064) Base Period Summary PMPMs: Net Medical PMPM ESRD( MA Base!D0065) Time Period Definition - Incurred From (MA Base!E0014) Time Period Definition - Incurred to (MA Base!E0015) Time Period Definition - Paid through( MA Base!E0016) Inpatient Facility Cost Sharing( MA Base!E0027) Skilled Nursing Facility Cost Sharing( MA Base!E0028) Home Health Cost Sharing( MA Base!E0029) Ambulance Cost Sharing( MA Base!E0030) DME/Prosthetics/Supplies Cost Sharing( MA Base!E0031) OP Facility - Emergency Cost Sharing( MA Base!E0032) OP Facility - Surgery Cost Sharing( MA Base!E0033) OP Facility - Other Cost Sharing( MA Base!E0034) Professional Cost Sharing( MA Base!E0035) Part B Rx Cost Sharing( MA Base!E0036) Other Medicare Covered Cost Sharing( MA Base!E0037) Transportation (Non-Covered) Cost Sharing( MA Base!E0038) Dental (Non-Covered) Cost Sharing( MA Base!E0039) Vision (Non-Covered) Cost Sharing( MA Base!E0040) Hearing (Non-Covered) Cost Sharing( MA Base!E0041) Health & Education Cost Sharing( MA Base!E0042) Other Non-Covered Cost Sharing( MA Base!E0043) COB/Subrg. (Outside Claim system) Cost Sharing( MA Base!E0044) Net Cost Sharing Total( MA Base!E0045) Base Period Summary CMS Revenue Hospice( MA Base!E0055) Base Period Summary Premium Revenue Hospice( MA Base!E0056) Base Period Summary Total Revenue Hospice( MA Base!E0057) Base Period Summary Net Medical Expenses Hospice( MA Base!E0059) Base Period Summary Member Months Hospice( MA Base!E0061) Base Period Summary PMPMs: Revenue PMPM Hospice( MA Base!E0064) Base Period Summary PMPMs: Net Medical PMPM Hospice( MA Base!E0065) Base Period Summary CMS Revenue All Other( MA Base!F0055) Base Period Summary Premium Revenue All Other( MA Base!F0056) Base Period Summary Total Revenue All Other( MA Base!F0057) Base Period Summary Net Medical Expenses All Other( MA Base!F0059) Base Period Summary Member Months All Other( MA Base!F0061) Base Period Summary PMPMs: Revenue PMPM All Other( MA Base!F0064) Base Period Summary PMPMs: Net Medical PMPM All Other( MA Base!F0065) Organization Name( MA Base!G0005) Plan Name( MA Base!G0006) Plan Type( MA Base!G0007) MA-PD( MA Base!G0008) Inpatient Facility Util/1000( MA Base!G0027) Skilled Nursing Facility Util/1000( MA Base!G0028) Home Health Util/1000( MA Base!G0029) Ambulance Util/1000( MA Base!G0030) DME/Prosthetics/Supplies Util/1000( MA Base!G0031) OP Facility - Emergency Util/1000( MA Base!G0032) OP Facility - Surgery Util/1000( MA Base!G0033) OP Facility - Other Util/1000( MA Base!G0034) Professional Util/1000( MA Base!G0035) Part B Rx Util/1000( MA Base!G0036) Other Medicare Covered Util/1000( MA Base!G0037) Transportation (Non-Covered) Util/1000( MA Base!G0038) Dental (Non-Covered) Util/1000( MA Base!G0039) Vision (Non-Covered) Util/1000( MA Base!G0040) Hearing (Non-Covered) Util/1000( MA Base!G0041) Health & Education Util/1000( MA Base!G0042) Other Non-Covered Util/1000( MA Base!G0043) Base Period Summary CMS Revenue Total( MA Base!G0055) Base Period Summary Premium Revenue Total( MA Base!G0056) Base Period Summary Total Revenue Total( MA Base!G0057) Base Period Summary Net Medical Expenses Total( MA Base!G0059) Base Period Summary Member Months Total( MA Base!G0061) Base Period Summary PMPMs: Revenue PMPM Total( MA Base!G0064) Base Period Summary PMPMs: Net Medical PMPM Total( MA Base!G0065) Base Period Summary PMPMs: Non-Benefit PMPM Total( MA Base!G0066) Base Period Summary PMPMs: Gain/(Loss) Margin PMPM Total( MA Base!G0067) Inpatient Facility Avg Cost( MA Base!H0027) Skilled Nursing Facility Avg Cost( MA Base!H0028) Home Health Avg Cost( MA Base!H0029) Ambulance Avg Cost( MA Base!H0030) DME/Prosthetics/Supplies Avg Cost( MA Base!H0031) OP Facility - Emergency Avg Cost( MA Base!H0032) OP Facility - Surgery Avg Cost( MA Base!H0033) OP Facility - Other Avg Cost( MA Base!H0034) Professional Avg Cost( MA Base!H0035) Part B Rx Avg Cost( MA Base!H0036) Other Medicare Covered Avg Cost( MA Base!H0037) Transportation (Non-Covered) Avg Cost( MA Base!H0038) Dental (Non-Covered) Avg Cost( MA Base!H0039) Vision (Non-Covered) Avg Cost( MA Base!H0040) Hearing (Non-Covered) Avg Cost( MA Base!H0041) Health & Education Avg Cost( MA Base!H0042) Other Non-Covered Avg Cost( MA Base!H0043) Total Member Months( MA Base!I0013) Total Non-ESRD Risk Score( MA Base!I0014) Total Completion Factor( MA Base!I0015) Inpatient Facility Allowed PMPM( MA Base!I0027) Skilled Nursing Facility Allowed PMPM( MA Base!I0028) Home Health Allowed PMPM( MA Base!I0029) Ambulance Allowed PMPM( MA Base!I0030) DME/Prosthetics/Supplies Allowed PMPM( MA Base!I0031) OP Facility - Emergency Allowed PMPM( MA Base!I0032) OP Facility - Surgery Allowed PMPM( MA Base!I0033) OP Facility - Other Allowed PMPM( MA Base!I0034) Professional Allowed PMPM( MA Base!I0035) Part B Rx Allowed PMPM( MA Base!I0036) Other Medicare Covered Allowed PMPM( MA Base!I0037) Transportation (Non-Covered) Allowed PMPM( MA Base!I0038) Dental (Non-Covered) Allowed PMPM( MA Base!I0039) Vision (Non-Covered) Allowed PMPM( MA Base!I0040) Hearing (Non-Covered) Allowed PMPM( MA Base!I0041) Health & Education Allowed PMPM( MA Base!I0042) Other Non-Covered Allowed PMPM( MA Base!I0043) COB/Subrg. Allowed PMPM( MA Base!I0044) Total Medical Expenses Allowed PMPM( MA Base!I0045) Sub-Total Medicare-covered Services Allowed PMPM( MA Base!I0047) Non-DE# Member Months( MA Base!J0013) Non-DE# Risk Score( MA Base!J0014) Inpatient Facility [Util/1000 Trend]( MA Base!J0027) Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) Home Health [Util/1000 Trend]( MA Base!J0029) Ambulance [Util/1000 Trend]( MA Base!J0030) DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) OP Facility - Other [Util/1000 Trend]( MA Base!J0034) Professional [Util/1000 Trend]( MA Base!J0035) Part B Rx [Util/1000 Trend]( MA Base!J0036) Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) Health & Education [Util/1000 Trend]( MA Base!J0042) Other Non-Covered [Util/1000 Trend]( MA Base!J0043) COB/Subrg. [Util/1000 Trend]( MA Base!J0044) Enrollee Type( MA Base!K0005) MA Region( MA Base!K0006) Act. Swap/Equiv. Indicator( MA Base!K0007) SNP Indicator( MA Base!K0008) DE# Member Months( MA Base!K0013) DE# Risk Score( MA Base!K0014) Inpatient Facility [Benefit Plan Change]( MA Base!K0027) Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) Home Health [Benefit Plan Change]( MA Base!K0029) Ambulance [Benefit Plan Change]( MA Base!K0030) DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) OP Facility - Other [Benefit Plan Change]( MA Base!K0034) Professional [Benefit Plan Change]( MA Base!K0035) Part B Rx [Benefit Plan Change]( MA Base!K0036) Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) Health & Education [Benefit Plan Change]( MA Base!K0042) Other Non-Covered [Benefit Plan Change]( MA Base!K0043) COB/Subrg. [Benefit Plan Change]( MA Base!K0044) Non-Benefit Expenses: Marketing & Sales( MA Base!K0056) Non-Benefit Expenses: Direct Admin( MA Base!K0057) Non-Benefit Expenses: Indirect Admin( MA Base!K0058) Non-Benefit Expenses: Net Cost of Private Reinsurance( MA Base!K0059) Insurer Fees( MA Base!K0060) Non-Benefit Expenses: Total Non-Benefit Expenses( MA Base!K0062) Inpatient Facility [Population Change]( MA Base!L0027) Skilled Nursing Facility [Population Change]( MA Base!L0028) Home Health [Population Change]( MA Base!L0029) Ambulance [Population Change]( MA Base!L0030) DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) OP Facility - Emergency [Population Change]( MA Base!L0032) OP Facility - Surgery [Population Change]( MA Base!L0033) OP Facility - Other [Population Change]( MA Base!L0034) Professional [Population Change]( MA Base!L0035) Part B Rx [Population Change]( MA Base!L0036) Other Medicare Covered [Population Change]( MA Base!L0037) Transportation (Non-Covered) [Population Change]( MA Base!L0038) Dental (Non-Covered) [Population Change]( MA Base!L0039) Vision (Non-Covered) [Population Change]( MA Base!L0040) Hearing (Non-Covered) [Population Change]( MA Base!L0041) Health & Education [Population Change]( MA Base!L0042) Other Non-Covered [Population Change]( MA Base!L0043) COB/Subrg. [Population Change]( MA Base!L0044) Inpatient Facility [Other Factor]( MA Base!M0027) Skilled Nursing Facility [Other Factor]( MA Base!M0028) Home Health [Other Factor]( MA Base!M0029) Ambulance [Other Factor]( MA Base!M0030) DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) OP Facility - Emergency [Other Factor]( MA Base!M0032) OP Facility - Surgery [Other Factor]( MA Base!M0033) OP Facility - Other [Other Factor]( MA Base!M0034) Professional [Other Factor]( MA Base!M0035) Part B Rx [Other Factor]( MA Base!M0036) Other Medicare Covered [Other Factor]( MA Base!M0037) Transportation (Non-Covered) [Other Factor]( MA Base!M0038) Dental (Non-Covered) [Other Factor]( MA Base!M0039) Vision (Non-Covered) [Other Factor]( MA Base!M0040) Hearing (Non-Covered) [Other Factor]( MA Base!M0041) Health & Education [Other Factor]( MA Base!M0042) Other Non-Covered [Other Factor]( MA Base!M0043) COB/Subrg. [Other Factor]( MA Base!M0044) Contract Plan ID - a( MA Base!N0014) Contract Plan ID - b( MA Base!N0015) Contract Plan ID - c( MA Base!N0016) Contract Plan ID - d( MA Base!N0017) Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) Region Name( MA Base!O0005) Region Name Sub Category 1( MA Base!O0006) Region Name Sub Category 2( MA Base!O0007) SNP Type( MA Base!O0008) Member Month Percentage - a( MA Base!O0014) Member Month Percentage - b( MA Base!O0015) Member Month Percentage - c( MA Base!O0016) Member Month Percentage - d( MA Base!O0017) Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) Non-Benefit Expenses: Gain(Loss) Margin( MA Base!O0055) Percent of Revenue: Net Medical Expenses( MA Base!O0058) Percent of Revenue: Non-Benefit Expenses( MA Base!O0059) Percent of Revenue: Gain/(Loss) Margin( MA Base!O0060) Contract Plan ID a( MA Base!P0014) Contract Plan ID b( MA Base!P0015) Contract Plan ID c( MA Base!P0016) Contract Plan ID d( MA Base!P0017) Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) Home Health [Additive Adjustment Util/1000]( MA Base!P0029) Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) Professional [Additive Adjustment Util/1000]( MA Base!P0035) Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) Medicaid Revenue( MA Base!P0063) Medicaid Cost( MA Base!P0064) Benefit Expenses( MA Base!P0065) Non-benefit Expenses( MA Base!P0066) Adjusted GLM( MA Base!P0067) EGWP:( MA Base!Q0008) Member Months a( MA Base!Q0014) Member Months b( MA Base!Q0015) Member Months c( MA Base!Q0016) Member Months d( MA Base!Q0017) Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) Home Health [Additive Adjustment PMPM]( MA Base!Q0029) Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) Professional [Additive Adjustment PMPM]( MA Base!Q0035) Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) bid_id HMO ID Organization name Parent Org Dec2015 enrollment 2015 Part C Star rating Spring State County Code 1( MA Bnchmk!B0039) State 1( MA Bnchmk!C0039) County Name 1( MA Bnchmk!D0039) County 1 - Projected Member Months( MA Bnchmk!E0039) County 1 - Projected Risk Factors( MA Bnchmk!F0039) County 1 - Plan Provided ISAR Factors for Risk Rates( MA Bnchmk!G0039) County 1 - MA Risk Ratebook Unadjusted( MA Bnchmk!H0039) County 1 - MA Risk Ratebook Risk-adjusted( MA Bnchmk!I0039) County 1 - ISAR Scale( MA Bnchmk!J0039) County 1 - ISAR-adjusted Bid( MA Bnchmk!K0039) County 1 - Risk Payment Rate A Only( MA Bnchmk!L0039) County 1 - Risk Payment Rate B Only( MA Bnchmk!M0039) County 1 - Original Medicare Cost Sharing Inpatient( MA Bnchmk!N0039) County 1 - Original Medicare Cost Sharing SNF( MA Bnchmk!O0039) County 1 - Weighted Average for Service Area - Original Medicare Cost Sharing Other Pt B( MA Bnchmk!P0039) County 1 - FFS costs to weight Inpatient( MA Bnchmk!Q0039) County 1 - FFS costs to weight SNF( MA Bnchmk!R0039) County 1 - FFS costs to weight Other Pt B( MA Bnchmk!S0039) County 1 - Metropolitan Statistical Area MM( MA Bnchmk!T0039) County 1 - Metropolitan Statistical Area MSA Name( MA Bnchmk!U0039) SSA State county code FIPS State county code
0 4754.0 919.0 2670.0 3548.0 12303.0 6726.0 2140.0 24508.0 25404.0 11604.0 10165.0 721.0 0.0 4269.0 53.0 715.0 0.0 H0151 1 0 2015 210.7875 22.178402 29.340429 7.272934 27.367561 18.218614 8.847904 103.794439 101.861536 28.230852 4.426344 2.692925 0.0 1.25962 0.000754 0.329534 0.0 0.0 566.6094 14939636.9 0.0 14939636.9 12576335.81 2156.688732 6927.1178 5831.3171 1/1/2013 12/31/2013 3/21/2014 22.5058 5.164165 0.0 3.401596 2.98235 2.032848 1.678272 14.930552 19.213499 5.419814 0.98473 0.0 0.0 0.533616 0.100347 0.0 0.0 0 78.9476 143569.0667 0.0 143569.0667 207433.0258 2726.134619 52.664 76.0905 214510435.2 0.0 214510435.2 170470426.8 300860.58 712.9895 566.6094 UNITEDHEALTHCARE OF ALABAMA, INC. AARP MedicareComplete Plan 1 (HMO) HMO Y 1833.067126 1002.050819 2328.10614 244.687388 3622.421446 477.382747 120.444486 21789.27608 13485.1476 2263.18779 692.747326 229.42188 0.0 240.483809 3.711546 40.683296 0.0 229593641.2 0.0 229593641.2 183254195.6 305743.4034 750.9357 599.3725 76.0188 75.5444 1527.233 327.439285 151.232428 523.502068 100.540187 509.06227 1048.733031 65.385371 107.740787 178.424434 93.732414 140.854477 0.0 89.481454 326.876068 97.199648 0.0 300860.58 1.004905 1.021063 233.2934 27.342567 29.340429 10.674529 30.349911 20.251462 10.526176 118.724992 121.075034 33.650667 5.411073 2.692925 0.0 1.793237 0.101101 0.329534 0.0 0.0 645.557 640.6402 266936.0164 0.961066 0.937118 1.007967 1.061817 1.061817 1.061817 1.061817 1.057846 1.016233 1.004594 1.009511 1.008621 1.061817 1.0 1.008621 1.008621 1.008621 1.0 1.0 A/B 0 N N 33924.56363 1.349853 0.995 1.028 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 1.0 0.98 0.98 0.98 1.0 1.0 5711524.233 12346048.07 5184675.755 0.0 0.0 23242248.06 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 1.0 0.980687 0.980687 0.980687 1.0 1.0 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.0 1.00015 1.00015 1.00015 1.0 1.0 H0151-001 0 0 0 1.026194 1.004906 1.001512 1.003724 0.762409 1.03258 1.004045 1.034831 1.030911 1.012546 0.999273 1.014148 1.0 1.059446 0.828348 1.011256 1.0 1.0 0 0 0 0 300860.58 0.0 0.0 0 1.048237 1.006122 1.019516 1.019516 1.019516 1.019516 1.010503 1.007876 0.997158 1.000264 0.996563 1.019516 1.0 0.996563 0.996563 0.996563 1.0 1.0 23097197.52 0.798168 0.101232 0.1006 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -8.9328 0.0 0.0 -234.15643 0.0 0.0 27.801641 0.0 0.0 2035473.818 4202603.765 3746630.405 455973.3605 20930067.57 N 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -0.034265 0.0 0.0 -2.841782 0.0 0.0 0.406603 0.0 0.0 0.0 H0151_001_0 H0151 UNITEDHEALTHCARE OF ALABAMA, INC. UnitedHealth Group, Inc. 33636 3 1480 AL Mobile 37415.17777 1.106654 0 789.21 873.382169 1.065793 680.025285 315.315285 364.71 0.092728 0.167257 0.2125 227.87 50.82 448.21 37415.17777 Mobile, AL 1480 1097
1 4754.0 919.0 2670.0 3548.0 12303.0 6726.0 2140.0 24508.0 25404.0 11604.0 10165.0 721.0 0.0 4269.0 53.0 715.0 0.0 H0151 1 0 2015 210.7875 22.178402 29.340429 7.272934 27.367561 18.218614 8.847904 103.794439 101.861536 28.230852 4.426344 2.692925 0.0 1.25962 0.000754 0.329534 0.0 0.0 566.6094 14939636.9 0.0 14939636.9 12576335.81 2156.688732 6927.1178 5831.3171 1/1/2013 12/31/2013 3/21/2014 22.5058 5.164165 0.0 3.401596 2.98235 2.032848 1.678272 14.930552 19.213499 5.419814 0.98473 0.0 0.0 0.533616 0.100347 0.0 0.0 0 78.9476 143569.0667 0.0 143569.0667 207433.0258 2726.134619 52.664 76.0905 214510435.2 0.0 214510435.2 170470426.8 300860.58 712.9895 566.6094 UNITEDHEALTHCARE OF ALABAMA, INC. AARP MedicareComplete Plan 1 (HMO) HMO Y 1833.067126 1002.050819 2328.10614 244.687388 3622.421446 477.382747 120.444486 21789.27608 13485.1476 2263.18779 692.747326 229.42188 0.0 240.483809 3.711546 40.683296 0.0 229593641.2 0.0 229593641.2 183254195.6 305743.4034 750.9357 599.3725 76.0188 75.5444 1527.233 327.439285 151.232428 523.502068 100.540187 509.06227 1048.733031 65.385371 107.740787 178.424434 93.732414 140.854477 0.0 89.481454 326.876068 97.199648 0.0 300860.58 1.004905 1.021063 233.2934 27.342567 29.340429 10.674529 30.349911 20.251462 10.526176 118.724992 121.075034 33.650667 5.411073 2.692925 0.0 1.793237 0.101101 0.329534 0.0 0.0 645.557 640.6402 266936.0164 0.961066 0.937118 1.007967 1.061817 1.061817 1.061817 1.061817 1.057846 1.016233 1.004594 1.009511 1.008621 1.061817 1.0 1.008621 1.008621 1.008621 1.0 1.0 A/B 0 N N 33924.56363 1.349853 0.995 1.028 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 1.0 0.98 0.98 0.98 1.0 1.0 5711524.233 12346048.07 5184675.755 0.0 0.0 23242248.06 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 1.0 0.980687 0.980687 0.980687 1.0 1.0 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.0 1.00015 1.00015 1.00015 1.0 1.0 H0151-001 0 0 0 1.026194 1.004906 1.001512 1.003724 0.762409 1.03258 1.004045 1.034831 1.030911 1.012546 0.999273 1.014148 1.0 1.059446 0.828348 1.011256 1.0 1.0 0 0 0 0 300860.58 0.0 0.0 0 1.048237 1.006122 1.019516 1.019516 1.019516 1.019516 1.010503 1.007876 0.997158 1.000264 0.996563 1.019516 1.0 0.996563 0.996563 0.996563 1.0 1.0 23097197.52 0.798168 0.101232 0.1006 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -8.9328 0.0 0.0 -234.15643 0.0 0.0 27.801641 0.0 0.0 2035473.818 4202603.765 3746630.405 455973.3605 20930067.57 N 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -0.034265 0.0 0.0 -2.841782 0.0 0.0 0.406603 0.0 0.0 0.0 H0151_001_0 H0151 UNITEDHEALTHCARE OF ALABAMA, INC. UnitedHealth Group, Inc. 33636 3 1010 AL Baldwin 24932.91624 1.106654 0 723.94 801.150882 0.977649 623.785184 289.235184 334.55 0.092728 0.167257 0.2125 227.87 50.82 448.21 28108.18573 Not in a Metro Area 1010 1003
2 4754.0 919.0 2670.0 3548.0 12303.0 6726.0 2140.0 24508.0 25404.0 11604.0 10165.0 721.0 0.0 4269.0 53.0 715.0 0.0 H0151 1 0 2015 210.7875 22.178402 29.340429 7.272934 27.367561 18.218614 8.847904 103.794439 101.861536 28.230852 4.426344 2.692925 0.0 1.25962 0.000754 0.329534 0.0 0.0 566.6094 14939636.9 0.0 14939636.9 12576335.81 2156.688732 6927.1178 5831.3171 1/1/2013 12/31/2013 3/21/2014 22.5058 5.164165 0.0 3.401596 2.98235 2.032848 1.678272 14.930552 19.213499 5.419814 0.98473 0.0 0.0 0.533616 0.100347 0.0 0.0 0 78.9476 143569.0667 0.0 143569.0667 207433.0258 2726.134619 52.664 76.0905 214510435.2 0.0 214510435.2 170470426.8 300860.58 712.9895 566.6094 UNITEDHEALTHCARE OF ALABAMA, INC. AARP MedicareComplete Plan 1 (HMO) HMO Y 1833.067126 1002.050819 2328.10614 244.687388 3622.421446 477.382747 120.444486 21789.27608 13485.1476 2263.18779 692.747326 229.42188 0.0 240.483809 3.711546 40.683296 0.0 229593641.2 0.0 229593641.2 183254195.6 305743.4034 750.9357 599.3725 76.0188 75.5444 1527.233 327.439285 151.232428 523.502068 100.540187 509.06227 1048.733031 65.385371 107.740787 178.424434 93.732414 140.854477 0.0 89.481454 326.876068 97.199648 0.0 300860.58 1.004905 1.021063 233.2934 27.342567 29.340429 10.674529 30.349911 20.251462 10.526176 118.724992 121.075034 33.650667 5.411073 2.692925 0.0 1.793237 0.101101 0.329534 0.0 0.0 645.557 640.6402 266936.0164 0.961066 0.937118 1.007967 1.061817 1.061817 1.061817 1.061817 1.057846 1.016233 1.004594 1.009511 1.008621 1.061817 1.0 1.008621 1.008621 1.008621 1.0 1.0 A/B 0 N N 33924.56363 1.349853 0.995 1.028 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 0.98 1.0 0.98 0.98 0.98 1.0 1.0 5711524.233 12346048.07 5184675.755 0.0 0.0 23242248.06 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 0.980687 1.0 0.980687 0.980687 0.980687 1.0 1.0 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.00015 1.0 1.00015 1.00015 1.00015 1.0 1.0 H0151-001 0 0 0 1.026194 1.004906 1.001512 1.003724 0.762409 1.03258 1.004045 1.034831 1.030911 1.012546 0.999273 1.014148 1.0 1.059446 0.828348 1.011256 1.0 1.0 0 0 0 0 300860.58 0.0 0.0 0 1.048237 1.006122 1.019516 1.019516 1.019516 1.019516 1.010503 1.007876 0.997158 1.000264 0.996563 1.019516 1.0 0.996563 0.996563 0.996563 1.0 1.0 23097197.52 0.798168 0.101232 0.1006 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -8.9328 0.0 0.0 -234.15643 0.0 0.0 27.801641 0.0 0.0 2035473.818 4202603.765 3746630.405 455973.3605 20930067.57 N 0 0 0 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 -0.034265 0.0 0.0 -2.841782 0.0 0.0 0.406603 0.0 0.0 0.0 H0151_001_0 H0151 UNITEDHEALTHCARE OF ALABAMA, INC. UnitedHealth Group, Inc. 33636 3 1500 AL Montgomery 23053.52626 1.106654 0 702.27 777.169696 0.948384 605.113160 280.583160 324.53 0.092728 0.167257 0.2125 227.87 50.82 448.21 40515.07715 Montgomery, AL 1500 1101

To use the library folium, we need this JSON file that contains the coordinates of each county that can be mapped to... well, the U.S. map.

In [ ]:
url = "https://raw.githubusercontent.com/python-visualization/folium/master/examples/data"
county_geo = f"{url}/us_counties_20m_topo.json"

geoJSON_df = gpd.read_file(county_geo)
geoJSON_df['id'] = geoJSON_df["id"].str[-5:].astype(int)
geoJSON_df.head(3)
Out[ ]:
id geometry
0 1001 POLYGON ((-86.49932 32.34394, -86.71470 32.402...
1 1009 POLYGON ((-86.57112 33.76632, -86.75060 33.841...
2 1017 POLYGON ((-85.17117 32.86797, -85.13527 32.771...

We'll define some functions we need to filter and scale the dataset.

In [ ]:
def filter_by_mco(df:pd.DataFrame, mco_type:str) -> pd.DataFrame:
    """Filter by plan type"""
    if mco_type != 'All':
        mco_df = df.loc[df['Plan Type( MA Base!G0007)'] == mco_type]
    else:
        mco_df = df
    return mco_df

def filter_by_org(df:pd.DataFrame, org:str) -> pd.DataFrame:
    """Filter by organization name"""
    if org != 'All':
        org_df = df.loc[df['Organization Name( MA Base!G0005)'] == org]
    else:
        org_df = df
    return org_df

def make_filtered_df(df:pd.DataFrame, mco_type:str, org:str) -> pd.DataFrame:
    """Wrap the filter by plan type and organization functions"""
    mco_df = filter_by_mco(df, mco_type)
    return filter_by_org(mco_df, org)

def filter_by_feat(df:pd.DataFrame, feat_of_interest:str) -> pd.DataFrame:
    """Filter by projection assumption"""
    return df.groupby("FIPS State county code")[feat_of_interest].mean()

def minmaxscale(df:pd.DataFrame) -> pd.DataFrame:
    """Performs min-max scaling across all data."""
    min_val = df.min()
    max_val = df.max()
    return (df - min_val) / (max_val - min_val)

def make_colorscale_df(df:pd.DataFrame, mco_type:str, org:str, feat_of_interest:str) -> pd.DataFrame:
    """Create a dataframe that will be used as the color scale."""
    mco_df = filter_by_mco(df, mco_type)
    org_df = filter_by_org(mco_df, org)
    feat_df = filter_by_feat(org_df, feat_of_interest)
    return minmaxscale(feat_df)

def create_overlay_data(geojson_df:pd.DataFrame, bpt_df:pd.DataFrame, feat_of_interest:str) -> pd.DataFrame:
    """Take the GeoJSON coordinates and the bid data, and perform a merge by FIPS"""

    tmp_df = bpt_df[['State 1( MA Bnchmk!C0039)', 
                    'County Name 1( MA Bnchmk!D0039)', 
                    'FIPS State county code', 
                    feat_of_interest]]

    tmp_df = tmp_df.groupby(['State 1( MA Bnchmk!C0039)', 
                    'County Name 1( MA Bnchmk!D0039)', 
                    'FIPS State county code'])[feat_of_interest].mean().reset_index()
    
    return pd.merge(geojson_df, tmp_df, 
                    how='inner', 
                    left_on='id', right_on='FIPS State county code')

colorscale = branca.colormap.linear.YlOrRd_09.scale()
def style_function(feature):
    """Note: color_df is declared outside of the function scope."""
    q = color_df.get(key=int(feature["id"][-5:]), default=None)
    return {
        "fillOpacity": 0.5,
        "weight": 0,
        "fillColor": "#black" if q is None else colorscale(q),
    }

def construct_map(county_json:dict, color_scheme, hover_data, feat_of_interest, alias):
    # Instantiate map object
    m = folium.Map(location=[37.0902, -95.7129], 
                tiles="cartodbpositron", 
                zoom_start=3)

    # Colorbar
    colormap = cm.linear.YlOrRd_09.scale(0, 1)
    colormap.caption = color_scheme.name
    m.add_child(colormap)

    # Create county-level visualization
    folium.TopoJson(
        data=json.loads(requests.get(county_json).text),
        object_path="objects.us_counties_20m",
        style_function=style_function 
    ).add_to(m)

    # Add hover attributes
    n = folium.features.GeoJson(
        data=hover_data.to_json(), 
        style_function=style_function_hover,
        control=False,
        highlight_function=highlight_function,
        tooltip=folium.features.GeoJsonTooltip(
            fields=['State 1( MA Bnchmk!C0039)', 
                    'County Name 1( MA Bnchmk!D0039)', 
                    feat_of_interest],
            aliases=['State: ', 
                    'County: ', 
                    alias],
            style=("background-color: white; \
                    color: #333333; \
                    font-family: arial; \
                    font-size: 12px; \
                    padding: 10px;") 
        )
    )

    # Add on the hover to the original map
    m.add_child(n)
    m.keep_in_front(n)
    return(m)

The code below all creates parameters for data visualization, such as the hover and the highlighted data.

Below, we're visualizing the Inpatient Facility Utility.

The object m is the folium object that has the mappings.

In [ ]:
style_function_hover = lambda x: {'fillColor': '#ffffff', 
                            'color':'#000000', 
                            'fillOpacity': 0.1, 
                            'weight': 0.1}

highlight_function = lambda x: {'fillColor': '#000000', 
                                'color':'#000000', 
                                'fillOpacity': 0.50, 
                                'weight': 0.1}
                                
color_df = make_colorscale_df(county_df, "All", "All", "Inpatient Facility Utilizers( MA Base!C0027)")
org_mco_df = make_filtered_df(county_df, "All", "All")

fulldf = create_overlay_data(geojson_df=geoJSON_df, 
                             bpt_df=org_mco_df, 
                             feat_of_interest='Inpatient Facility Utilizers( MA Base!C0027)')
m = construct_map(county_json=county_geo, color_scheme=color_df, hover_data=fulldf, feat_of_interest='Inpatient Facility Utilizers( MA Base!C0027)', alias='Inpatient Facility: ')
m
Out[ ]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Summary¶

  • This notebook provides a high-level overview of the BPT data and its structure
  • We provide a Python script (bpt.py) that allows the analyst to access the zipped data objects and read them into Pandas dataframes for downstream analyses.